<%@ page import="javax.sql.*"%>
<%@ taglib uri="http://struts.apache.org/tags-bean" prefix="bean"%>
<%@ taglib uri="http://struts.apache.org/tags-html" prefix="html"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Application Reviewer</title>
</head>
<body>
Reviewer List: <p>
<table>
			<tr>
				<th>Name</th>
				<th>Ungraded</th>
				<th>Graded</th>
			</tr>
	
	
	<%-- Import the java.sql package --%>
	<%@ page import="java.sql.*"%>
	<%-- -------- Open Connection Code -------- --%>
	<%
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			// Registering Postgresql JDBC driver with the DriverManager
			Class.forName("org.postgresql.Driver");

			// Open a connection to the database using DriverManager
			conn = DriverManager
					.getConnection("jdbc:postgresql://localhost/admissions?"
							+ "user=postgres&password=27428572");
	%>
	<%
		// Begin transaction
			//insert Address
			conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT user_ref, user_name FROM user_roles WHERE role='reviewer'");
			if(rs != null)
				while(rs.next())
				{
					%>
					<tr>
					
					<td><%=rs.getString(2)%></td>
					<% 
					int userId = rs.getInt(1);
					Statement stmt2 = conn.createStatement();
					ResultSet rs2 = stmt2.executeQuery("SELECT id FROM list_application WHERE graded='false'AND reviewer_id='"+userId+"'");
					int ungraded = 0;
					if(rs2 != null)
						while(rs2.next())
							ungraded++;
					%>
					<td><html:link action="/protected/admins/ungraded">
						<html:param name="ungraded" value="<%=Integer.toString(ungraded)%>"/>
						<html:param name="id" value="<%=Integer.toString(userId)%>"/>
						<%=ungraded%></html:link></td>
					<%
					rs2 = stmt2.executeQuery("SELECT id FROM list_application WHERE graded='true' AND reviewer_id='"+userId+"'");
					int graded = 0;
					if(rs2 != null)
						while(rs2.next())
							graded++;	
					%>
					<td><html:link action="/protected/admins/graded">
						<html:param name="graded" value="<%=Integer.toString(graded)%>"/>
						<html:param name="id" value="<%=Integer.toString(userId)%>"/>
						<%=graded%></html:link></td>
					
					</tr>
					<%
				}
			conn.setAutoCommit(true);
			
		  }

		catch (SQLException e){
			throw new RuntimeException(e);
		}

		finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
				}
				rs = null;
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
				}
				pstmt = null;
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
				conn = null;
			}

		}
		%>
		
		</table>
</body>
</html>